import pandas as pd
import mysql_util as sql_util
import mysql_util_starrocks as starrocks_util
import json
import math

"""查询用户是否分配配权限"""


def test(uid):
    sql = "select count(1) total from master_data.cp_utm_event_log a where 1= 1 and a.create_time > '2024-09-16' and a.appid = '401' and a.uid = '{}' \
        and a.utm_souce in ('Broilerchicken_Numberofuserentries')".format(uid)
    rs = starrocks_util.select_by_sql(sql)
    rs = json.loads(rs)
    # print(rs)
    return rs[0]['total']


if __name__ == '__main__':
    file_path = 'C:/Users/yangjianzhang/Desktop/farm_info_0256.xlsx'
    # 使用read_excel函数读取Excel文件并将其存储为DataFrame对象
    df = pd.read_excel(file_path)

    # 打印读取到的数据
    # print(df)
    print(type(df))
    org_codes = []
    farm_orgs = []
    total_list = []
    ur_list = []
    tr_list = []
    tp_list = []
    cp_list = []

    for index, row in df.iterrows():
        org_code = row['ORG_CODE']
        farm_org = row['FARM_ORG']
        date_in = row['DATE_IN']
        date_close = row['DATE_CLOSE']
        house_code = farm_org.split('-')[1]
        total = 0
        ur = 0
        tr = 0
        tp = 0
        cp = 0
        sql = "select a.ZCOMPF_CODE,a.ZCOMPF_NAME,a.FARM_CODE,a.FARM_NAME,a.HOUSE_CODE,a.HOUSE_NAME,a.`status`\
            ,count(1) total\
            ,sum(case when a.`status` = 'TR' then 1 else 0 end ) tr\
            ,sum(case when a.`status` = 'CP' then 1 else 0 end ) cp\
            ,sum(case when a.`status` = 'TP' then 1 else 0 end ) tp\
            ,sum(case when a.`status` = 'UR' then 1 else 0 end ) ur\
            from sb_dwm_pig_pression_pb01 a \
            where a.ZBUSI_CODE = 'Z13EN'\
            and a.FARM_CODE = '{}' and a.HOUSE_CODE = '{}'\
            and a.create_time between '{} 00:00:00' and '{} 23:23:59'\
            group by a.ZCOMPF_CODE,a.ZCOMPF_NAME,a.FARM_CODE,a.FARM_NAME,a.HOUSE_CODE,a.HOUSE_NAME\
            order by a.ZCOMPF_CODE,a.ZCOMPF_NAME,a.FARM_CODE,a.FARM_NAME,a.HOUSE_CODE,a.HOUSE_NAME".format(
            org_code,
            house_code,
            date_in,
            date_close)
        rs = sql_util.select_by_sql(sql)
        rs = json.loads(rs)
        print(org_code + '-' + house_code + '-' + str(rs))
        if len(rs) > 0:
            total = rs[0]['total']
            ur = rs[0]['ur']
            tr = rs[0]['tr']
            tp = rs[0]['tp']
            cp = rs[0]['cp']

        org_codes.append(org_code)
        farm_orgs.append(farm_org)
        total_list.append(total)
        ur_list.append(ur)
        tr_list.append(tr)
        tp_list.append(tp)
        cp_list.append(cp)

    excel_data = {}
    excel_data['ORG_CODE'] = org_codes
    excel_data['FARM_ORG'] = farm_orgs
    excel_data['全部'] = total_list
    excel_data['已处理'] = cp_list
    excel_data['未读'] = ur_list
    excel_data['超时未接收'] = tr_list
    excel_data['超时未处理'] = tp_list

    df = pd.DataFrame(excel_data)
    path = "C:/Users/yangjianzhang/Desktop/"
    df.to_excel(path + 'farm_info_z13_0256.xlsx', index=False)
